from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String,select



def create_table():
    """create table"""
    metadata = MetaData()
    users = Table('users', metadata,
                  Column('id', Integer, primary_key=True),
                  Column('name', String(50)),
                  Column('fullname', String(50)),
                  mysql_engine='InnoDB',
                  mysql_charset='utf8'
                  )

    # Column('id', Integer, Sequence('user_id_seq'), primary_key=True)
    engine = create_engine('mysql://stock:stockjj@127.0.0.1/test?charset=utf8', encoding='utf-8', convert_unicode=True,
                           echo=True)
    metadata.create_all(engine)


def auto_load_table(db_engine):
    """自动映射表"""
    # reflect the table into sqlalchemy
    # we should use the meta to do the reflect in version 0.9
    meta = MetaData()
    users = Table("users", meta, autoload=True, autoload_with=db_engine)  # 这句话就提供了一个表名，其他的，sqlalchemy都帮你做完了
    s = select([users])

    conn = db_engine.connect()
    result = conn.execute(s)

    for row in result:
        print(row)

    result.close()
    conn.close()


def query_simple(db_engine):
    """简单查询范例"""
    # reflect the table into sqlalchemy
    # we should use the meta to do the reflect in version 0.9
    meta = MetaData()
    users = Table("users", meta, autoload=True, autoload_with=db_engine)  # 这句话就提供了一个表名，其他的，sqlalchemy都帮你做完了

    # check all the column names and do a select to fetch the data directly from table
    conn = db_engine.connect()
    s = select([users]).where(users.c.name == 'jack')  # 提供查询条件
    result = conn.execute(s)
    for row in result:
        print(row[users.c.name], row[users.c.fullname])  # 看这里，直接用t.c.name就可以调用name列的值了，c代表column。不用做映射，不用配置文件，简单到无语吧？...

    result.close()
    # remember to close the cursor
    result.close()


if __name__ == '__main__':
    engine = create_engine('mysql://stock:stockjj@127.0.0.1/test?charset=utf8', echo=True)
    # auto_load_table(engine)
    query_simple(engine)
